--    Program: 		CAPARIMP_CUSTOMER_CONTACT_POINT.sql
--    Description:  Import AR CUSTOMER CONTACT POINT information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         1-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_contact_point_id		number;
   l_owner_table_id			number;
                    		                 		
   cursor cur_ar_customer_contact is
      select * from CRST_HZ_CONTACT_POINTS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_customer_contact loop   	
     l_err_msg_tmp := '';
     
     select HZ_CONTACT_POINTS_S.NEXTVAL 
     into l_contact_point_id
     from dual;
     
     update CRST_HZ_CONTACT_POINTS 
     set    contact_point_id_n = l_contact_point_id
     where  contact_point_id = cur.contact_point_id;
     
     if cur.owner_table_name = 'HZ_PARTIES' then
       select CAPARIMP_MAPPING.GetHZPartyIDMapping(cur.owner_table_id)
       into   l_owner_table_id
       from   dual;	
   --elsif cur.owner_table_name = 'HZ_PARTY_SITES' then
     else 
       select CAPARIMP_MAPPING.GetHZPartySiteIDMapping(cur.owner_table_id)
       into   l_owner_table_id
       from   dual;	
     end if;
     	
     begin
       -- import data
       insert into HZ_CONTACT_POINTS
       (
              CONTACT_POINT_ID              ,
              CONTACT_POINT_TYPE            ,
              STATUS                        ,
              OWNER_TABLE_NAME              ,
              OWNER_TABLE_ID                ,
              PRIMARY_FLAG                  ,
              ORIG_SYSTEM_REFERENCE         ,
              LAST_UPDATE_DATE              ,
              LAST_UPDATED_BY               ,
              CREATION_DATE                 ,
              CREATED_BY                    ,
              LAST_UPDATE_LOGIN             ,
              WH_UPDATE_DATE                ,
              REQUEST_ID                    ,
              PROGRAM_APPLICATION_ID        ,
              PROGRAM_ID                    ,
              PROGRAM_UPDATE_DATE           ,
              ATTRIBUTE_CATEGORY            ,
              ATTRIBUTE1                    ,
              ATTRIBUTE2                    ,
              ATTRIBUTE3                    ,
              ATTRIBUTE4                    ,
              ATTRIBUTE5                    ,
              ATTRIBUTE6                    ,
              ATTRIBUTE7                    ,
              ATTRIBUTE8                    ,
              ATTRIBUTE9                    ,
              ATTRIBUTE10                   ,
              ATTRIBUTE11                   ,
              ATTRIBUTE12                   ,
              ATTRIBUTE13                   ,
              ATTRIBUTE14                   ,
              ATTRIBUTE15                   ,
              ATTRIBUTE16                   ,
              ATTRIBUTE17                   ,
              ATTRIBUTE18                   ,
              ATTRIBUTE19                   ,
              ATTRIBUTE20                   ,
              GLOBAL_ATTRIBUTE_CATEGORY     ,
              GLOBAL_ATTRIBUTE1             ,
              GLOBAL_ATTRIBUTE2             ,
              GLOBAL_ATTRIBUTE3             ,
              GLOBAL_ATTRIBUTE4             ,
              GLOBAL_ATTRIBUTE5             ,
              GLOBAL_ATTRIBUTE6             ,
              GLOBAL_ATTRIBUTE7             ,
              GLOBAL_ATTRIBUTE8             ,
              GLOBAL_ATTRIBUTE9             ,
              GLOBAL_ATTRIBUTE10            ,
              GLOBAL_ATTRIBUTE11            ,
              GLOBAL_ATTRIBUTE12            ,
              GLOBAL_ATTRIBUTE13            ,
              GLOBAL_ATTRIBUTE14            ,
              GLOBAL_ATTRIBUTE15            ,
              GLOBAL_ATTRIBUTE16            ,
              GLOBAL_ATTRIBUTE17            ,
              GLOBAL_ATTRIBUTE18            ,
              GLOBAL_ATTRIBUTE19            ,
              GLOBAL_ATTRIBUTE20            ,
              EDI_TRANSACTION_HANDLING      ,
              EDI_ID_NUMBER                 ,
              EDI_PAYMENT_METHOD            ,
              EDI_PAYMENT_FORMAT            ,
              EDI_REMITTANCE_METHOD         ,
              EDI_REMITTANCE_INSTRUCTION    ,
              EDI_TP_HEADER_ID              ,
              EDI_ECE_TP_LOCATION_CODE      ,
              EMAIL_FORMAT                  ,
              EMAIL_ADDRESS                 ,
              BEST_TIME_TO_CONTACT_START    ,
              BEST_TIME_TO_CONTACT_END      ,
              PHONE_CALLING_CALENDAR        ,
              CONTACT_ATTEMPTS              ,
              CONTACTS                      ,
              DECLARED_BUSINESS_PHONE_FLAG  ,
              DO_NOT_USE_FLAG               ,
              DO_NOT_USE_REASON             ,
              LAST_CONTACT_DT_TIME          ,
              PHONE_PREFERRED_ORDER         ,
              PRIORITY_OF_USE_CODE          ,
              TELEPHONE_TYPE                ,
              TIME_ZONE                     ,
              PHONE_TOUCH_TONE_TYPE_FLAG    ,
              PHONE_AREA_CODE               ,
              PHONE_COUNTRY_CODE            ,
              PHONE_NUMBER                  ,
              PHONE_EXTENSION               ,
              PHONE_LINE_TYPE               ,
              TELEX_NUMBER                  ,
              WEB_TYPE                      ,
              URL                           ,
              CONTENT_SOURCE_TYPE           ,
              RAW_PHONE_NUMBER              ,
              OBJECT_VERSION_NUMBER         ,
              CREATED_BY_MODULE             ,
              APPLICATION_ID                ,
              TIMEZONE_ID                   ,
              CONTACT_POINT_PURPOSE         ,
              PRIMARY_BY_PURPOSE            ,
              TRANSPOSED_PHONE_NUMBER       ,
              EFT_TRANSMISSION_PROGRAM_ID   ,
              EFT_PRINTING_PROGRAM_ID       ,
              EFT_USER_NUMBER               ,
              EFT_SWIFT_CODE                ,
              ACTUAL_CONTENT_SOURCE          
       )
       values
       (
                l_contact_point_id                ,     
                cur.CONTACT_POINT_TYPE            ,     
                cur.STATUS                        ,     
                cur.OWNER_TABLE_NAME              ,     
                l_owner_table_id                  ,     
                cur.PRIMARY_FLAG                  ,     
                cur.ORIG_SYSTEM_REFERENCE         ,     
                cur.LAST_UPDATE_DATE              ,     
                CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),     
                cur.CREATION_DATE                 ,     
                CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),     
                cur.LAST_UPDATE_LOGIN             ,     
                cur.WH_UPDATE_DATE                ,     
                cur.REQUEST_ID                    ,     
                cur.PROGRAM_APPLICATION_ID        ,     
                cur.PROGRAM_ID                    ,     
                cur.PROGRAM_UPDATE_DATE           ,     
                cur.ATTRIBUTE_CATEGORY            ,     
                cur.ATTRIBUTE1                    ,     
                cur.ATTRIBUTE2                    ,     
                cur.ATTRIBUTE3                    ,     
                cur.ATTRIBUTE4                    ,     
                cur.ATTRIBUTE5                    ,     
                cur.ATTRIBUTE6                    ,     
                cur.ATTRIBUTE7                    ,     
                cur.ATTRIBUTE8                    ,     
                cur.ATTRIBUTE9                    ,     
                cur.ATTRIBUTE10                   ,     
                cur.ATTRIBUTE11                   ,     
                cur.ATTRIBUTE12                   ,     
                cur.ATTRIBUTE13                   ,     
                cur.ATTRIBUTE14                   ,     
                cur.ATTRIBUTE15                   ,     
                cur.ATTRIBUTE16                   ,     
                cur.ATTRIBUTE17                   ,     
                cur.ATTRIBUTE18                   ,     
                cur.ATTRIBUTE19                   ,     
                cur.ATTRIBUTE20                   ,     
                cur.GLOBAL_ATTRIBUTE_CATEGORY     ,     
                cur.GLOBAL_ATTRIBUTE1             ,     
                cur.GLOBAL_ATTRIBUTE2             ,     
                cur.GLOBAL_ATTRIBUTE3             ,     
                cur.GLOBAL_ATTRIBUTE4             ,     
                cur.GLOBAL_ATTRIBUTE5             ,     
                cur.GLOBAL_ATTRIBUTE6             ,     
                cur.GLOBAL_ATTRIBUTE7             ,     
                cur.GLOBAL_ATTRIBUTE8             ,     
                cur.GLOBAL_ATTRIBUTE9             ,     
                cur.GLOBAL_ATTRIBUTE10            ,     
                cur.GLOBAL_ATTRIBUTE11            ,     
                cur.GLOBAL_ATTRIBUTE12            ,     
                cur.GLOBAL_ATTRIBUTE13            ,     
                cur.GLOBAL_ATTRIBUTE14            ,     
                cur.GLOBAL_ATTRIBUTE15            ,     
                cur.GLOBAL_ATTRIBUTE16            ,     
                cur.GLOBAL_ATTRIBUTE17            ,     
                cur.GLOBAL_ATTRIBUTE18            ,     
                cur.GLOBAL_ATTRIBUTE19            ,     
                cur.GLOBAL_ATTRIBUTE20            ,     
                cur.EDI_TRANSACTION_HANDLING      ,     
                cur.EDI_ID_NUMBER                 ,     
                cur.EDI_PAYMENT_METHOD            ,     
                cur.EDI_PAYMENT_FORMAT            ,     
                cur.EDI_REMITTANCE_METHOD         ,     
                cur.EDI_REMITTANCE_INSTRUCTION    ,     
                cur.EDI_TP_HEADER_ID              ,     
                cur.EDI_ECE_TP_LOCATION_CODE      ,     
                cur.EMAIL_FORMAT                  ,     
                cur.EMAIL_ADDRESS                 ,     
                cur.BEST_TIME_TO_CONTACT_START    ,     
                cur.BEST_TIME_TO_CONTACT_END      ,     
                cur.PHONE_CALLING_CALENDAR        ,     
                cur.CONTACT_ATTEMPTS              ,     
                cur.CONTACTS                      ,     
                cur.DECLARED_BUSINESS_PHONE_FLAG  ,     
                cur.DO_NOT_USE_FLAG               ,     
                cur.DO_NOT_USE_REASON             ,     
                cur.LAST_CONTACT_DT_TIME          ,     
                cur.PHONE_PREFERRED_ORDER         ,     
                cur.PRIORITY_OF_USE_CODE          ,     
                cur.TELEPHONE_TYPE                ,     
                cur.TIME_ZONE                     ,     
                cur.PHONE_TOUCH_TONE_TYPE_FLAG    ,     
                cur.PHONE_AREA_CODE               ,     
                cur.PHONE_COUNTRY_CODE            ,     
                cur.PHONE_NUMBER                  ,     
                cur.PHONE_EXTENSION               ,     
                cur.PHONE_LINE_TYPE               ,     
                cur.TELEX_NUMBER                  ,     
                cur.WEB_TYPE                      ,     
                cur.URL                           ,     
                cur.CONTENT_SOURCE_TYPE           ,     
                cur.RAW_PHONE_NUMBER              ,     
                cur.OBJECT_VERSION_NUMBER         ,     
                cur.CREATED_BY_MODULE             ,     
                cur.APPLICATION_ID                ,     
                cur.TIMEZONE_ID                   ,     
                cur.CONTACT_POINT_PURPOSE         ,     
                cur.PRIMARY_BY_PURPOSE            ,     
                cur.TRANSPOSED_PHONE_NUMBER       ,     
                cur.EFT_TRANSMISSION_PROGRAM_ID   ,     
                cur.EFT_PRINTING_PROGRAM_ID       ,     
                cur.EFT_USER_NUMBER               ,     
                cur.EFT_SWIFT_CODE                ,     
                cur.ACTUAL_CONTENT_SOURCE                                 
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
     commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_CONTACT_POINT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
